from datetime import datetime
import happybase
from fastapi import Depends, FastAPI, HTTPException
from pydantic import BaseModel
from fastapi.middleware.cors import CORSMiddleware
import models, schemas, crud
from database import SessionLocal, engine

models.Base.metadata.create_all(bind=engine)

app = FastAPI()

# 定义允许访问该 API 的来源列表
origins = [
    "http://localhost",
    "http://localhost:8989",
]

# 添加中间件处理跨域资源共享 (CORS)
# 这允许从指定的来源访问 API
app.add_middleware(
    CORSMiddleware,
    allow_origins=origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# 定义一个 Pydantic 模型用于验证请求数据
class Query(BaseModel):
    start_date: str
    end_date: str
    platform: int
    highest_rank: int
    hot_content: str
def convert_date_to_number(time_date):
    """
    将日期转换为数字表示形式
    :param time_date: 日期字符串
    :return: 日期的数字表示形式
    """
    datetime_obj = datetime.strptime(time_date, "%Y/%m/%d")
    year = datetime_obj.year
    month = datetime_obj.month
    day = datetime_obj.day
    numeric_date = year * 10000 + month * 100 + day
    return numeric_date

def get_db():
    """
    连接MySQL数据库
    :return: 返回数据库
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.post("/get_data_from_hbase")
async def process_request(request_data: Query):
    """
    通过HBase检索数据
    :param request_data: 请求参数
    :return: 查询到的数据
    """
    # hbase数据库链接
    connection = happybase.Connection('192.168.139.136')
    table_name = 'hot_search'
    table = connection.table(table_name)
    # 构造查询条件
    start_date = request_data.start_date
    end_date = request_data.end_date
    platform = request_data.platform
    highest_rank = request_data.highest_rank
    hot_content = request_data.hot_content
    # 处理查询参数
    if start_date:
        start_date = convert_date_to_number(start_date)
    if end_date:
        end_date = convert_date_to_number(end_date)
        
    # 构建过滤参数
    filter_str = ""
    if start_date:
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', >=, 'binary:{str(start_date)}')"
    if end_date:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', <=, 'binary:{str(end_date)}')"
    if platform != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'platform', =, 'binary:{str(platform)}')"
    if highest_rank != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'highest_rank', =, 'binary:{str(highest_rank)}')"
    if hot_content:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'hot_content', =, 'substring:{str(hot_content)}')"
    print(filter_str)
    # 执行查询
    scan = table.scan(filter=filter_str, limit=500)

    formatted_results = []
    for key, data in scan:
        row_data = {}
        for column, value in data.items():
            column_str = column.decode()
            if column_str.startswith('cf:'):
                column_parts = column_str.split(':')
                if len(column_parts) > 1:
                    new_column = column_parts[1]
                    row_data[new_column] = value.decode()
        formatted_results.append(row_data)

    formatted_results = sorted(formatted_results, key=lambda x: x['search_index_peak'], reverse=True)
    
    # 关闭连接
    connection.close()
    return formatted_results

@app.post("/word_count")
async def process_request(request_data: Query):
    """
    查询并处理数据
    :param request_data: 请求参数
    :return: 查询到的数据
    """
    
    # hbase数据库链接
    connection = happybase.Connection('192.168.139.136')
    table_name = 'hot_search'
    table = connection.table(table_name)
    # 构造查询条件
    start_date = request_data.start_date
    end_date = request_data.end_date
    platform = request_data.platform
    highest_rank = request_data.highest_rank
    hot_content = request_data.hot_content
    # 处理查询参数
    if start_date:
        start_date = convert_date_to_number(start_date)
    if end_date:
        end_date = convert_date_to_number(end_date)
        
    # 构建过滤参数
    filter_str = ""
    if start_date:
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', >=, 'binary:{str(start_date)}')"
    if end_date:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', <=, 'binary:{str(end_date)}')"
    if platform != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'platform', =, 'binary:{str(platform)}')"
    if highest_rank != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'highest_rank', =, 'binary:{str(highest_rank)}')"
    if hot_content:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'hot_content', =, 'substring:{str(hot_content)}')"
    print(filter_str)
    # 执行查询
    scan = table.scan(filter=filter_str, limit=50)

    formatted_results = []
    for key, data in scan:
        row_data = {}
        for column, value in data.items():
            column_str = column.decode()
            if column_str.startswith('cf:'):
                column_parts = column_str.split(':')
                if len(column_parts) > 1:
                    new_column = column_parts[1]
                    if new_column == "search_index_peak" or new_column == "duration_number":
                        row_data[new_column] = float(value.decode())
                    else:
                        row_data[new_column] = value.decode()
        formatted_results.append(row_data)

    # sorted_results = sorted(formatted_results, key=lambda x: x['search_index_peak'], reverse=True)
    # top_100_results = sorted_results[:80]

    # 关闭连接
    connection.close()
    return formatted_results

@app.post("/line_chart")
async def process_request(request_data: Query):
    """
    在HBase中提取数据并格式化成响应体，为线性图做准备
    :param request_data: 请求参数
    :return: 构建的响应体
    """
    # hbase数据库链接
    connection = happybase.Connection('192.168.139.136')
    table_name = 'hot_search'
    table = connection.table(table_name)
    # 构造查询条件
    start_date = request_data.start_date
    end_date = request_data.end_date
    platform = request_data.platform
    highest_rank = request_data.highest_rank
    hot_content = request_data.hot_content
    # 处理查询参数
    if start_date:
        start_date = convert_date_to_number(start_date)
    if end_date:
        end_date = convert_date_to_number(end_date)
    # 构建过滤参数
    filter_str = ""
    if start_date:
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', >=, 'binary:{str(start_date)}')"
    if end_date:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', <=, 'binary:{str(end_date)}')"
    if platform != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'platform', =, 'binary:{str(platform)}')"
    if highest_rank != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'highest_rank', =, 'binary:{str(highest_rank)}')"
    if hot_content:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'hot_content', =, 'substring:{str(hot_content)}')"
    print(filter_str)
    # 执行查询
    scan = table.scan(filter=filter_str)

    formatted_results = []
    for key, data in scan:
        row_data = {}
        for column, value in data.items():
            column_str = column.decode()
            if column_str.startswith('cf:'):
                column_parts = column_str.split(':')
                if len(column_parts) > 1:
                    new_column = column_parts[1]
                    if new_column == "ranking_time":
                        time = str(value)
                        time = time.strip("b'")  # 去除字符串中的字符'b'和单引号
                        try:
                            time1 = datetime.strptime(time, "%Y-%m-%d %H:%M")
                            formatted_time = datetime.strftime(time1, "%Y-%m-%d, %H:%M:%S")
                        except ValueError:
                            try:
                                time1 = datetime.strptime(time, "%Y/%m/%d %H:%M")
                                formatted_time = datetime.strftime(time1, "%Y-%m-%d, %H:%M:%S")
                            except ValueError:
                                formatted_time = time
                        row_data[new_column] = formatted_time
                    elif new_column == "search_index_peak" or new_column == "duration_number":
                        row_data[new_column] = float(value.decode())
                    else:
                        row_data[new_column] = value.decode()
        formatted_results.append(row_data)

    sorted_results = sorted(formatted_results, key=lambda x: x["ranking_time"])
    peak = []
    time = []
    content = []

    for result in sorted_results:
        peak.append(result["search_index_peak"])
        time.append(result["ranking_time"])
        content.append(result["hot_content"])
    response = {
        "peak": peak,
        "time": time,
        "content": content
    }
    # 关闭连接
    connection.close()
    return response

@app.post("/radar_chart")
async def process_request(request_data: Query):
    """
    在HBase中提取数据并格式化，为雷达图做准备
    :param request_data: 请求参数
    :return: 构建好的数据
    """
    # hbase数据库链接
    connection = happybase.Connection('192.168.139.136')
    table_name = 'hot_search'
    table = connection.table(table_name)
    # 构造查询条件
    start_date = request_data.start_date
    end_date = request_data.end_date
    platform = request_data.platform
    highest_rank = request_data.highest_rank
    hot_content = request_data.hot_content
    # 处理查询参数
    if start_date:
        start_date = convert_date_to_number(start_date)
    if end_date:
        end_date = convert_date_to_number(end_date)
    # 构建过滤参数
    filter_str = ""
    if start_date:
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', >=, 'binary:{str(start_date)}')"
    if end_date:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'date_number', <=, 'binary:{str(end_date)}')"
    if platform != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'platform', =, 'binary:{str(platform)}')"
    if highest_rank != 0:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'highest_rank', =, 'binary:{str(highest_rank)}')"
    if hot_content:
        if filter_str:
            filter_str += " AND "
        filter_str += f"SingleColumnValueFilter('cf', 'hot_content', =, 'substring:{str(hot_content)}')"
    print(filter_str)
    # 执行查询
    scan = table.scan(filter=filter_str)

    formatted_results = []
    for key, data in scan:
        row_data = {}
        for column, value in data.items():
            column_str = column.decode()
            if column_str.startswith('cf:'):
                column_parts = column_str.split(':')
                if len(column_parts) > 1:
                    new_column = column_parts[1]
                    if new_column == "ranking_time":
                        time = str(value)
                        time = time.strip("b'")  # 去除字符串中的字符'b'和单引号
                        try:
                            time1 = datetime.strptime(time, "%Y-%m-%d %H:%M")
                            formatted_time = datetime.strftime(time1, "%Y-%m-%d, %H:%M:%S")
                        except ValueError:
                            try:
                                time1 = datetime.strptime(time, "%Y/%m/%d %H:%M")
                                formatted_time = datetime.strftime(time1, "%Y-%m-%d, %H:%M:%S")
                            except ValueError:
                                formatted_time = time
                        row_data[new_column] = formatted_time
                    elif new_column == "search_index_peak" or new_column == "duration_number":
                        row_data[new_column] = float(value.decode())
                    else:
                        row_data[new_column] = value.decode()
        formatted_results.append(row_data)

    # 创建字典用于存储每个平台的最大搜索指数和持续时间
    platform_data = {}

    # 遍历每个结果
    for result in formatted_results:
        platform = result['platform']
        search_index_peak = float(result['search_index_peak'])
        duration_number = int(result['duration_number'])
        highest_rank = int(result['highest_rank'])
        hot_content = str(result['hot_content'])
        platform_dict = {1: "百度", 2: "抖音", 3: "微博", 4: "知乎"}

        # 检查是否存在该平台的数据
        if platform in platform_data:
            max_search_index = platform_data[platform]['search_index_peak']
            max_duration = platform_data[platform]['duration_number']

            # 更新最大搜索指数和持续时间
            if search_index_peak > max_search_index:
                platform_data[platform] = {
                    'search_index_peak': search_index_peak,
                    'duration_number': duration_number,
                    'highest_rank': highest_rank,
                    'hot_content': platform_dict.get(int(platform))+":"+hot_content
                }
            elif search_index_peak == max_search_index and duration_number > max_duration:
                platform_data[platform] = {
                    'search_index_peak': search_index_peak,
                    'duration_number': duration_number,
                    'highest_rank': highest_rank,
                    'hot_content': platform_dict.get(int(platform))+":"+hot_content
                 }
        else:
            platform_data[platform] = {
                'search_index_peak': search_index_peak,
                'duration_number': duration_number,
                'highest_rank': highest_rank,
                'hot_content': platform_dict.get(int(platform))+":"+hot_content
            }

    # 输出每个平台的最大搜索指数和持续时间
    for platform, data in platform_data.items():
        print("平台:", platform)
        print("最大搜索指数:", data['search_index_peak'])
        print("持续时间:", data['duration_number'])
        print("榜单最高位:", data['highest_rank'])
        print("热搜内容:", data['hot_content'])
        print()

    converted_data = []

    for platform, data in platform_data.items():
        platform_name = data["hot_content"]
        highest_rank = data["highest_rank"]
        search_index_peak = data["search_index_peak"]
        duration_number = data["duration_number"]

        converted_data.append({
            "name": platform_name,
            "value": [highest_rank, round(search_index_peak/100, 2), round(duration_number/3600, 2)]
        })
    # 关闭连接
    connection.close()
    return converted_data





